package com.iscas.green.extralindextest.service;

import com.mongodb.client.MongoCollection;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.redis.connection.RedisConnection;
import org.springframework.data.redis.connection.lettuce.LettuceConnectionFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;


import lombok.extern.slf4j.Slf4j;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.bson.Document;

@Slf4j
@Service
public class DataService {
    
    private final JdbcTemplate mysqlJdbcTemplate;
    private final JdbcTemplate postgresqlJdbcTemplate;
    private final JdbcTemplate clickhouseJdbcTemplate;
    private final MongoTemplate mongoTemplate;
    private final RedisTemplate<String, Object> redisTemplate;
    private final LettuceConnectionFactory lettuceConnectionFactory;
    
    @Autowired
    public DataService(
            @Qualifier("mysqlJdbcTemplate") JdbcTemplate mysqlJdbcTemplate,
            @Qualifier("postgresqlJdbcTemplate") JdbcTemplate postgresqlJdbcTemplate,
            @Qualifier("clickhouseJdbcTemplate") JdbcTemplate clickhouseJdbcTemplate,
            MongoTemplate mongoTemplate,
            RedisTemplate<String, Object> redisTemplate,
            LettuceConnectionFactory lettuceConnectionFactory) {
        this.mysqlJdbcTemplate = mysqlJdbcTemplate;
        this.postgresqlJdbcTemplate = postgresqlJdbcTemplate;
        this.clickhouseJdbcTemplate = clickhouseJdbcTemplate;
        this.mongoTemplate = mongoTemplate;
        this.redisTemplate = redisTemplate;
        this.lettuceConnectionFactory = lettuceConnectionFactory;
    }
    
    // MySQL操作示例
    public void testMysql() {
        // 创建一个测试表并测试基本操作
        log.info("开始测试MySQL连接");
        try {
            // 首先尝试一个简单的只读查询来测试连接
            List<Map<String, Object>> serverInfo = mysqlJdbcTemplate.queryForList("SELECT version() as version, database() as db");
            log.info("MySQL连接成功，版本: {}, 当前数据库: {}", 
                    serverInfo.get(0).get("version"), serverInfo.get(0).get("db"));
                    
            // 检查是否有权限创建和操作表
            boolean canCreateTable = true;
            try {
                // 先检查表是否存在
                List<Map<String, Object>> tableCheck = mysqlJdbcTemplate.queryForList(
                        "SELECT COUNT(*) as table_count FROM information_schema.tables WHERE table_name = 'test_mysql' AND table_schema = database()");
                boolean tableExists = ((Number) tableCheck.get(0).get("table_count")).intValue() > 0;
                
                if (!tableExists) {
                    mysqlJdbcTemplate.execute("CREATE TABLE test_mysql (id INT PRIMARY KEY, data VARCHAR(100))");
                    log.info("MySQL测试表创建成功");
                } else {
                    log.info("MySQL测试表已存在");
                }
            } catch (Exception e) {
                canCreateTable = false;
                log.warn("无权创建MySQL表或表操作失败，将尝试只读操作: {}", e.getMessage());
            }
            
            if (canCreateTable) {
                // 如果有表操作权限，执行写入和查询测试
                mysqlJdbcTemplate.update("INSERT INTO test_mysql (id, data) VALUES (1, '测试数据') ON DUPLICATE KEY UPDATE data='测试数据'");
                List<Map<String, Object>> results = mysqlJdbcTemplate.queryForList("SELECT * FROM test_mysql WHERE id = 1");
                
                if (!results.isEmpty()) {
                    Map<String, Object> row = results.get(0);
                    log.info("MySQL测试成功，查询结果: {}", row);
                } else {
                    log.info("MySQL测试成功，但查询结果为空");
                }
            } else {
                // 只读测试 - 查询一些系统信息
                List<Map<String, Object>> tableInfo = mysqlJdbcTemplate.queryForList(
                        "SELECT table_name FROM information_schema.tables WHERE table_schema = database() LIMIT 5");
                log.info("MySQL只读连接测试成功，数据库中的表: {}", tableInfo);
            }
        } catch (Exception e) {
            log.error("MySQL测试过程中出错", e);
            throw e;
        }
    }
    
    // PostgreSQL操作示例
    public void testPostgresql() {
        // 创建一个测试表并测试基本操作
        log.info("开始测试PostgreSQL连接");
        try {
            // 首先尝试查询服务器版本和当前数据库，确认连接可用
            try {
                List<Map<String, Object>> dbInfo = postgresqlJdbcTemplate.queryForList(
                        "SELECT version() as version, current_database() as database");
                log.info("PostgreSQL连接成功，版本: {}, 当前数据库: {}", 
                        dbInfo.get(0).get("version"), 
                        dbInfo.get(0).get("database"));
            } catch (Exception e) {
                log.error("PostgreSQL基础连接测试失败: {}", e.getMessage());
                throw e; // 如果基础连接失败，直接抛出异常
            }
            
            // 检查数据库是否有权限创建表
            boolean canCreateTable = true;
            try {
                // 先检查是否存在test_postgresql表
                List<Map<String, Object>> tableCheck = postgresqlJdbcTemplate.queryForList(
                        "SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_name = 'test_postgresql') AS table_exists");
                boolean tableExists = (boolean) tableCheck.get(0).get("table_exists");
                
                if (!tableExists) {
                    postgresqlJdbcTemplate.execute("CREATE TABLE test_postgresql (id SERIAL PRIMARY KEY, data VARCHAR(100))");
                    log.info("已创建test_postgresql表");
                } else {
                    log.info("test_postgresql表已存在");
                }
            } catch (Exception e) {
                canCreateTable = false;
                log.warn("无权创建表或表操作失败，将尝试只读操作: {}", e.getMessage());
            }
            
            if (canCreateTable) {
                // 如果有权限，插入测试数据
                postgresqlJdbcTemplate.update("INSERT INTO test_postgresql (data) VALUES ('测试数据')");
                List<Map<String, Object>> results = postgresqlJdbcTemplate.queryForList("SELECT * FROM test_postgresql LIMIT 1");
                
                if (!results.isEmpty()) {
                    Map<String, Object> row = results.get(0);
                    log.info("PostgreSQL测试成功，查询结果: {}", row);
                } else {
                    log.info("PostgreSQL测试成功，但查询结果为空");
                }
            } else {
                // 如果没有权限，使用只读查询测试连接
                List<Map<String, Object>> tables = postgresqlJdbcTemplate.queryForList(
                        "SELECT tablename FROM pg_tables WHERE schemaname = 'public' LIMIT 10");
                log.info("PostgreSQL只读连接测试成功，数据库中的表: {}", tables);
            }
        } catch (Exception e) {
            log.error("PostgreSQL测试过程中出错", e);
            throw e;
        }
    }
    
    /**
     * 在PostgreSQL中创建表并插入数据
     * @param data 要插入的数据
     * @return 操作结果
     */
    public ResponseEntity<String> insertPostgresqlData(String data) {
        try {
            postgresqlJdbcTemplate.update("CREATE TABLE IF NOT EXISTS chaos_test (id SERIAL PRIMARY KEY, data TEXT)");
            postgresqlJdbcTemplate.update("INSERT INTO chaos_test (data) VALUES (?)", data);

            return ResponseEntity.ok("Successfully inserted data into PostgreSQL");
        } catch (Exception e) {
            log.error("PostgreSQL operation failed", e);
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(e.getMessage());
        }
    }
    
    // ClickHouse操作示例
    public void testClickhouse() {
        // 测试ClickHouse连接和基本操作
        log.info("开始测试ClickHouse连接");
        try {
            // 先尝试使用只读查询
            List<Map<String, Object>> versionResults = clickhouseJdbcTemplate.queryForList("SELECT version() as version");
            log.info("ClickHouse连接成功，版本: {}", versionResults.get(0).get("version"));
            
            // 尝试查询otel_logs表
            try {
                log.info("尝试查询default.otel_logs表");
                Map<String, Object> result = clickhouseJdbcTemplate.queryForMap(
                        "SELECT * FROM default.otel_logs LIMIT 1"
                );
                log.info("ClickHouse otel_logs表查询成功，结果: {}", result);
            } catch (Exception e) {
                log.warn("查询otel_logs表失败: {}", e.getMessage());
                
                // 如果无法查询otel_logs表，使用系统表进行只读测试
                List<Map<String, Object>> databases = clickhouseJdbcTemplate.queryForList("SELECT name FROM system.databases LIMIT 5");
                log.info("ClickHouse只读连接测试成功，可见数据库: {}", databases);
            }
        } catch (Exception e) {
            log.error("ClickHouse测试过程中出错", e);
            throw e;
        }
    }
    
    /**
     * 从ClickHouse查询数据
     * @return 查询结果
     */
    public Map<String, Object> getClickhouseData() {
        log.info("开始查询ClickHouse数据");
        try {
            // 从ClickHouse的otel_logs表中获取第一条数据
            Map<String, Object> result = clickhouseJdbcTemplate.queryForMap(
                    "SELECT * FROM default.otel_logs LIMIT 1"
            );
            
            log.info("ClickHouse数据查询成功: {}", result);
            return result;
        } catch (Exception e) {
            log.error("ClickHouse数据查询失败: {}", e.getMessage(), e);
            // 返回一个带有错误信息的结果，而不是抛出异常
            Map<String, Object> errorResult = new HashMap<>();
            errorResult.put("error", "ClickHouse查询失败: " + e.getMessage());
            return errorResult;
        }
    }
    
    // MongoDB操作示例
    public void testMongoDB() {
        log.info("开始测试MongoDB连接");
        try {
            // 获取集合列表，以测试连接
            Set<String> collectionNames = mongoTemplate.getCollectionNames();
            log.info("MongoDB连接成功，可用集合: {}", collectionNames);
            
            // 尝试读取test集合中的数据
            List<Document> documents = mongoTemplate.findAll(Document.class, "test");
            log.info("MongoDB test集合中有 {} 条数据", documents.size());
            
            // 打印第一条数据的内容（如果有）
            if (!documents.isEmpty()) {
                log.info("MongoDB第一条数据: {}", documents.get(0).toJson());
            }
        } catch (Exception e) {
            log.error("MongoDB测试过程中出错", e);
            throw e;
        }
    }
    
    /**
     * 获取MongoDB集合中的数据
     * @return 包含数据计数和文档列表的响应
     */
    public ResponseEntity<?> getMongoData() {
        log.info("开始获取MongoDB集合数据");
        try {
            // 获取所有集合名称
            Set<String> collectionNames = mongoTemplate.getCollectionNames();
            log.info("MongoDB可用集合: {}", collectionNames);
            
            // 尝试获取名为"1"的集合中的数据
            MongoCollection<Document> collection = mongoTemplate.getCollection("test");
            List<Document> documents = collection.find().limit(10).into(new ArrayList<>());

            Map<String, Object> response = new HashMap<>();
            response.put("count", documents.size());
            response.put("data", documents);
            
            log.info("MongoDB数据获取成功，共 {} 条记录", documents.size());
            return ResponseEntity.ok(response);
        } catch (Exception e) {
            log.error("MongoDB数据获取失败", e);
            return ResponseEntity.internalServerError()
                    .body("Error retrieving data: " + e.getMessage());
        }
    }
    
    /**
     * 测试Redis Lettuce客户端
     * @param value 要存储的值
     * @return 操作结果
     */
    public ResponseEntity<String> testLettuce(String value) {
        log.info("开始测试Redis Lettuce客户端连接...");
        try {
            // 首先尝试获取连接
            log.info("尝试获取Redis连接...");
            RedisConnection connection = lettuceConnectionFactory.getConnection();
            
            if (connection == null) {
                log.error("无法获取Redis连接，连接为null");
                return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                        .body("Failed to get Redis connection: connection is null");
            }
            
            try {
                // 测试设置值
                log.info("尝试设置Redis键值: key=lettuce-key, value={}", value);
                connection.set("lettuce-key".getBytes(), value.getBytes());
                
                // 测试获取值
                byte[] retrievedBytes = connection.get("lettuce-key".getBytes());
                if (retrievedBytes == null) {
                    log.warn("从Redis获取的值为null");
                    return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                            .body("Failed to retrieve value from Redis: null value");
                }
                
                String retrievedValue = new String(retrievedBytes);
                log.info("从Redis成功获取值: {}", retrievedValue);
                
                return ResponseEntity.ok("Successfully tested Lettuce client with value: " + retrievedValue);
            } finally {
                // 确保关闭连接
                try {
                    log.info("关闭Redis连接");
                    connection.close();
                } catch (Exception e) {
                    log.warn("关闭Redis连接时发生异常", e);
                }
            }
        } catch (Exception e) {
            log.error("Redis Lettuce操作失败: {}", e.getMessage(), e);
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
                    .body("Lettuce error: " + e.getMessage());
        }
    }
}